//
//  DB.m
//  lianliankan
//
//  Created by iMac-hxsd on 11-12-18.
//  Copyright 2011年 __MyCompanyName__. All rights reserved.
//

#import "DB.h"
#import "MiscHelper.h"
#import "SQLiteHelper.h"

@implementation DB

static sqlite3 *kLLKDatabase;
static SQLiteHelper *kSqlite;

static sqlite3_stmt *fetchUserStatement = nil;
static sqlite3_stmt *updateUserStatement = nil;
static sqlite3_stmt *fetchGameLevelStatement = nil;
static sqlite3_stmt *fetchUserGameStatement = nil;
static sqlite3_stmt *newUserGameStatement = nil;
static sqlite3_stmt *updateUserGameStatement = nil;

// Finalize (delete) all of the SQLite compiled queries.
+ (void) finalizeStatements
{
	if (fetchUserStatement)
	{
		sqlite3_finalize(fetchUserStatement);
	}
	if (updateUserStatement)
	{
		sqlite3_finalize(updateUserStatement);
	}
	if (fetchGameLevelStatement)
	{
		sqlite3_finalize(fetchGameLevelStatement);
	}
	if (fetchUserGameStatement)
	{
		sqlite3_finalize(fetchUserGameStatement);
	}
	if (fetchUserGameStatement)
	{
		sqlite3_finalize(fetchUserGameStatement);
	}
	if (updateUserGameStatement)
	{
		sqlite3_finalize(updateUserGameStatement);
	}
	
	[kSqlite closeDatabase];
}

// 单例
+ (id) singleton
{
	return [[[self alloc] init] autorelease];
}

-(id) init
{
	if ((self=[super init]) ) {
		if (kLLKDatabase == nil)
		{
			if (kSqlite == nil) {
				kSqlite = [[SQLiteHelper alloc] init];
			}
			[kSqlite createEditableCopyOfDatabaseIfNeeded];
			[kSqlite initDatabaseConnection];
			
			kLLKDatabase = [kSqlite database];
		}
	}
	
	return self;
}

// 获取玩家信息
+ (User *) fetchUser
{
	User *user = [[User alloc] init];
	
	// Compile the query for retrieving data.
	if (fetchUserStatement == nil) 
    {
		const char *sql = "SELECT music, efx, gamelevelid FROM user LIMIT 1";
		if (sqlite3_prepare_v2(kLLKDatabase, sql, -1, &fetchUserStatement, NULL) != SQLITE_OK) {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kLLKDatabase));
		}
	}
	
	if (sqlite3_step(fetchUserStatement) == SQLITE_ROW)
	{
		user.music = (Boolean) sqlite3_column_int(fetchUserStatement, 0);
		user.efx = (Boolean) sqlite3_column_int(fetchUserStatement, 1);
		user.gameLevelId = sqlite3_column_int(fetchUserStatement, 2);
	}
	else
	{
		user.music = NO;
		user.efx = NO;
		user.gameLevelId = 1;
	}
	// Reset the statement for future reuse.
	sqlite3_reset(fetchUserStatement);
	
	return user;
}

// 更新玩家信息
+ (void) updateUser: (User *)user
{
	if (updateUserStatement == nil) {
		static char *sql = "UPDATE user SET music=?, efx=?, gamelevelid=?";
		if (sqlite3_prepare_v2(kLLKDatabase, sql, -1, &updateUserStatement, NULL) != SQLITE_OK) {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kLLKDatabase));
		}
	}
	sqlite3_bind_int(updateUserStatement, 1, (int) user.music);
	sqlite3_bind_int(updateUserStatement, 2, (int) user.efx);
	sqlite3_bind_int(updateUserStatement, 3, user.gameLevelId);
	
	int success = sqlite3_step(updateUserStatement);
	
	if (success == SQLITE_ERROR) {
		NSAssert1(0, @"Error: failed to updateUser with message '%s'.", sqlite3_errmsg(kLLKDatabase));
	}
	
	sqlite3_reset(updateUserStatement);
}

// 获取游戏关卡数量
+ (NSUInteger) fetchGameLevelTotal
{
	sqlite3_stmt *statement;
	
	const char *sql = "SELECT COUNT(*) AS amount FROM gamelevels";
	if (sqlite3_prepare_v2(kLLKDatabase, sql, -1, &statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kLLKDatabase));
	}
	
	NSUInteger levelTotal = 0;	
	if (sqlite3_step(statement) == SQLITE_ROW)
	{
		levelTotal = sqlite3_column_int(statement, 0);
	}
	
	sqlite3_finalize(statement);
	
	return levelTotal;
}

// 获取某关游戏数据
+ (GameLevel *) fetchGameLevel: (int)level
{
	GameLevel *gl = [[GameLevel alloc] init];
	
	// Compile the query for retrieving data.
	if (fetchGameLevelStatement == nil) {
		const char *sql = "SELECT * FROM gamelevels WHERE gamelevelid = ?";
		if (sqlite3_prepare_v2(kLLKDatabase, sql, -1, &fetchGameLevelStatement, NULL) != SQLITE_OK) {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kLLKDatabase));
		}
	}
	// For this query, we bind the primary key to the first (and only) placeholder in the statement.
	// Note that the parameters are numbered from 1, not from 0.
	sqlite3_bind_int(fetchGameLevelStatement, 1, level);
	
	if (sqlite3_step(fetchGameLevelStatement) == SQLITE_ROW)
	{
		gl.gameLevelId = level;
		gl.imageNumber = sqlite3_column_int(fetchGameLevelStatement, 1);
		gl.gameTime = sqlite3_column_int(fetchGameLevelStatement, 2);
		gl.refreshNumber = sqlite3_column_int(fetchGameLevelStatement, 3);
		gl.finderNumber = sqlite3_column_int(fetchGameLevelStatement, 4);
		gl.bombNumber = sqlite3_column_int(fetchGameLevelStatement, 5);
	}
	else
	{
		gl.gameLevelId = 0;
		gl.imageNumber = 0;
		gl.gameTime = 0;
		gl.refreshNumber = 0;
		gl.finderNumber = 0;
		gl.bombNumber = 0;
	}
	// Reset the statement for future reuse.
	sqlite3_reset(fetchGameLevelStatement);
	
	return gl;
}

// 获取玩家某关游戏数据
+ (UserGame *) fetchUserGame: (int)level
{
	UserGame *ug = [[UserGame alloc] init];
	
	// Compile the query for retrieving data.
	if (fetchUserGameStatement == nil) {
		const char *sql = "SELECT usergame.*, gamelevels.gametime FROM usergame LEFT JOIN gamelevels ON (usergame.gamelevelid = gamelevels.gamelevelid) WHERE usergame.gamelevelid=?";
		if (sqlite3_prepare_v2(kLLKDatabase, sql, -1, &fetchUserGameStatement, NULL) != SQLITE_OK) {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kLLKDatabase));
		}
	}
	// For this query, we bind the primary key to the first (and only) placeholder in the statement.
	// Note that the parameters are numbered from 1, not from 0.
	sqlite3_bind_int(fetchUserGameStatement, 1, level);
	
	if (sqlite3_step(fetchUserGameStatement) == SQLITE_ROW)
	{
		ug.gameLevelId = level;
		ug.spentTime = sqlite3_column_int(fetchUserGameStatement, 2);
		ug.score = sqlite3_column_int(fetchUserGameStatement, 3);
		ug.passed = (BOOL) sqlite3_column_int(fetchUserGameStatement, 4);
	}
	else
	{
		ug.spentTime = 0;
		ug.score = 0;
		ug.passed = NO;
	}
	// Reset the statement for future reuse.
	sqlite3_reset(fetchUserGameStatement);
	
	return ug;
}

// 过关后，记录玩家数据
+ (void) newUserGame: (UserGame *) userGame
{
	if (newUserGameStatement == nil) {
		static char *sql = "INSERT INTO usergame(gamelevelid,spenttime,score,passed) VALUES(?,?,?,?)";
		if (sqlite3_prepare_v2(kLLKDatabase, sql, -1, &newUserGameStatement, NULL) != SQLITE_OK) {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kLLKDatabase));
		}
	}
	
	sqlite3_bind_int(newUserGameStatement, 1, userGame.gameLevelId);
	sqlite3_bind_int(newUserGameStatement, 2, userGame.spentTime);
	sqlite3_bind_int(newUserGameStatement, 3, userGame.score);
	sqlite3_bind_int(newUserGameStatement, 4, (int) userGame.passed);
	
	int success = sqlite3_step(newUserGameStatement);
	
	if (success == SQLITE_ERROR) {
		NSAssert1(0, @"Error: failed to newUserGame with message '%s'.", sqlite3_errmsg(kLLKDatabase));
	}
	
	sqlite3_reset(newUserGameStatement);
}

// 更新玩家数据
+ (void) updateUserGame: (UserGame *) userGame
{
	if (updateUserGameStatement == nil) {
		static char *sql = "UPDATE usergame SET spenttime=?, score=? WHERE gamelevelid=?";
		if (sqlite3_prepare_v2(kLLKDatabase, sql, -1, &updateUserGameStatement, NULL) != SQLITE_OK) {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kLLKDatabase));
		}
	}
	
	sqlite3_bind_int(updateUserGameStatement, 1, userGame.spentTime);
	sqlite3_bind_int(updateUserGameStatement, 2, userGame.score);
	sqlite3_bind_int(updateUserGameStatement, 3, userGame.gameLevelId);
	
	int success = sqlite3_step(updateUserGameStatement);
	
	if (success == SQLITE_ERROR) {
		NSAssert1(0, @"Error: failed to updateUserGame with message '%s'.", sqlite3_errmsg(kLLKDatabase));
	}
	
	sqlite3_reset(updateUserGameStatement);
}

@end
